package model.admin;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.opensymphony.xwork2.Result;
import com.sun.xml.internal.bind.v2.runtime.unmarshaller.XsiNilLoader.Array;


import myutil.Connect;
import vo.BatchDetailsVo;
import vo.mixed.*;
import vo.*;
public class GetBatchListMo {
	ArrayList<BatchDetailsVo> batch_list=new ArrayList<BatchDetailsVo>();
	public ArrayList<BatchDetailsVo> getBatchList() throws Exception{
		/*
		 * this method gets all the batches in the college for admin
		 * */	
			
			Connect c=new Connect();
			ResultSet rs=c.getResult("select * from batch_details where status='Active'");
			try {
				while(rs.next()){
			
				BatchDetailsVo single_object=new BatchDetailsVo();
				single_object.setBatch_id(rs.getInt("batch_id"));
				single_object.setBatch_name(rs.getString("batch_name"));
				single_object.setDescription(rs.getString("description"));
				single_object.setStudent_count(rs.getInt("student_count"));
				
				batch_list.add(single_object);
				System.out.println("siva "+single_object.getBatch_name());
				
			}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
			return batch_list;
			
		}
	
	
public ArrayList<DegreeListVo> getAddDegreeToBatch(int batch_id){
	ArrayList<DegreeListVo> degree = new ArrayList<DegreeListVo>();
	String query="";
	Connect c = new Connect();
	if (batch_id==0){
		query="select degree_id,degree_name from degree_details where status='Active'";
	}else {
		query="SELECT degree_id, degree_name FROM degree_details WHERE STATUS = 'Active' AND degree_id NOT IN ("
				+" SELECT degree_id FROM degree_department WHERE batch_id ="+batch_id+" )";
	}
	System.out.println(query);
	try {
		ResultSet rs=c.getResult(query);
		while(rs.next()){
			DegreeListVo vo = new DegreeListVo();
			vo.setDegree_id(rs.getInt("degree_id"));
			vo.setDegree_name(rs.getString("degree_name"));
			vo.setBatch_id(batch_id);
			degree.add(vo);
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return degree;
}
	
public ArrayList<DepartmentListVo> getBatchDepartment(int batch_id){
	ArrayList<DepartmentListVo> dep = new ArrayList<DepartmentListVo>();
	String query;
	Connect c= new Connect();
	query="";
	try {
		ResultSet rs= c.getResult(query);
		while(rs.next()){
			
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	
	return dep;
}
	

public ArrayList<DepartmentDetailsVo> getAddDepartmenttoDegreeList(int degree_id){
	ArrayList<DepartmentDetailsVo> dep = new ArrayList<DepartmentDetailsVo>();
	String query="";
	Connect c= new Connect();
	query="SELECT department_id, department_name FROM department_details WHERE status='Active' and department_id NOT IN ( SELECT DISTINCT department_id FROM degree_department_mgmt WHERE status='Active' and degree_id ="+degree_id+")";
	try {
		ResultSet rs= c.getResult(query);
		while(rs.next()){
		DepartmentDetailsVo vo = new DepartmentDetailsVo();
		vo.setDepartment_id(rs.getInt("department_id"));
		vo.setDepartment_name(rs.getString("department_name"));
		vo.setDegree_id(degree_id);
		dep.add(vo);
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return dep;
}
	//Function to get List of department in a batch
		public ArrayList<DepartmentListVo> getDepartmentList(int batch_id, int degree_id, int department_id) throws Exception{
			/*
			 * This method gets the departments present for one batch selected, or degree
			 */
			ArrayList<DepartmentListVo> dep_list=new ArrayList<DepartmentListVo>();
			Connect c=new Connect();
			String query="";
			if (degree_id==0 && batch_id==0 && department_id==0)
				query="select d.department_id," +
						"	  d.department_name, " +
						"	  d.description " +
						"from department_details d where status='Active'";
			else if (degree_id==0 && batch_id!=0 && department_id==0)
					query="	select distinct b.batch_name," +
									"d.department_id," +
									"d.department_name,		" +
									"d.description" +
							 " from department_details d," +
						  			"degree_department dd," +
						  			"batch_details b " +
						  	"where 	d.department_id	=	dd.department_id  " +
						  	  "and 	b.batch_id	    =	dd.batch_id   " +
						  	  "and  b.batch_id="+batch_id+";";
			else if(department_id!=0 && batch_id==0 && degree_id==0){
				query="select d.department_id,d.department_name,d.description,d.start_year,d.status from department_details d where department_id="+department_id+"";
			}
			else
				    query="SELECT dp.department_id, department_name,dp.description FROM department_details dp, degree_department_mgmt dpm WHERE degree_id ="+degree_id+" AND dpm.status = 'Active' AND dp.department_id = dpm.department_id";
			System.out.println(query);
			ResultSet rs=c.getResult(query);
			try {
			while(rs.next()){
				DepartmentListVo single_object=new DepartmentListVo();
				single_object.setBatch_id(batch_id);
				//single_object.setBatch_name(rs.getString("b.batch_name"));
				single_object.setDepartment_id(rs.getInt("department_id"));
				single_object.setDepartment_name(rs.getString("department_name"));
				single_object.setDescription(rs.getString("description"));
				System.out.println(single_object.getDepartment_name()+" Dept name ");
				 
//				single_object.setCourseList(getBatchCourseList(batch_id, single_object.getDepartment_id()));
				
				dep_list.add(single_object);
				
			}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				dep_list=null;
			}
			return dep_list;
	}
		
// Function to get Course list for a particular batch
		public  ArrayList<CourseListVo> getBatchCourseList(int batch_id, int department_id) throws Exception{
			ArrayList<CourseListVo> batch_department_list = new ArrayList<CourseListVo>(); 
			Connect c=new Connect();
			String query="";
	/*		if(batch_id==0)
		  query="select c.course_name," +
		    	" c.course_code," +
		    	" c.course_id , " +
		    	" dd.department_name, " +
		    	" dd.department_id" +
           " from degree_department d, " +
           		" course_details c, " +
           		" department_details dd " +
	       "where d.department_id	=	dd.department_id " +
	       	 "and c.department_id	=	d.department_id " +
	       	 "and c.department_id		=	"+department_id+";";
			else				
			query="select c.course_name," +
		    	" c.course_code," +
		    	" c.course_id , " +
		    	" dd.department_name, " +
		    	" dd.department_id" +
           " from degree_department d, " +
           		" course_details c, " +
           		" department_details dd " +
	       "where d.department_id	=	dd.department_id " +
	       	 "and c.department_id	=	d.department_id " +
	       	 "and d.batch_id		=	"+batch_id+";";
	       	 
	       	 */
			if (department_id==0){
				query="SELECT DISTINCT course_name, cd.course_code, cd.course_id, department_name FROM course_details cd, department_details dd, course_management cm "
						+"WHERE cm.batch_id ="+batch_id+" AND cm.course_id = cd.course_id AND cd.department_id = dd.department_id";
			}else if(batch_id==0){
				query="select course_name, course_code,cd.course_id,department_name from course_details cd,department_details dd where cd.department_id="+department_id+" and cd.department_id=dd.department_id";
			}
			
	       	 System.out.println(query);
			ResultSet rs= null;
			try {
				rs = c.getResult(query);		
				while(rs.next()){
					CourseListVo single_object = new CourseListVo();
					single_object.setCourse_code(rs.getString("course_code"));
					single_object.setCourse_id(rs.getInt("course_id"));
					single_object.setDepartment_name(rs.getString("department_name"));
					single_object.setDepartment_id(department_id);
					single_object.setCourse_name(rs.getString("course_name"));
					
					batch_department_list.add(single_object);
					
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return batch_department_list;
		}
		
public ArrayList<CourseListVo> getDepartmentCourseList(int department_id){
	ArrayList< CourseListVo> courselist = new ArrayList<CourseListVo>();
	String query ="";
	Connect c= new Connect();
	query="SELECT course_id, course_name FROM course_details cd WHERE status='Active' and department_id !="+department_id;
	System.out.println(query);
	try {
		ResultSet rs= c.getResult(query);
		while(rs.next()){
			CourseListVo vo = new CourseListVo();
			vo.setCourse_id(rs.getInt("course_id"));
			vo.setCourse_name(rs.getString("course_name"));
			vo.setDepartment_id(department_id);
			courselist.add(vo);
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return courselist;
}
		
public ArrayList<ProfessorListVo> getBatchProfessorList(int batch_id)
		{
			ArrayList<ProfessorListVo> batch_professor_list= new ArrayList<ProfessorListVo>();
			
			String query="select cs.staff_name," +
								 "pw.patent," +
								 "pw.research," +
								 "pw.project" +
						  " from college_staff cs," +
						  	     "professor_work pw  " +
						  "where cs.staff_id	=	pw.professor_id " +
						  "  and cs.staff_id in (select pd.professor_id" +
						  						"  from professor_department pd," +
						  								"degree_department dd " +
						  						 "where dd.department_id	=	pd.department_id " +
						  						   "and batch_id="+batch_id+");";
			Connect c=new Connect();
			ResultSet rs= null;
			try {
				rs = c.getResult(query);		
				while(rs.next()){
					
					ProfessorListVo single_object = new ProfessorListVo();
					single_object.setPatent(rs.getString("pw.patent"));
					single_object.setProject(rs.getString("pw.project"));
					single_object.setResearch(rs.getString("pw.research"));
					single_object.setStaff_name(rs.getString("cs.staff_name"));
					
					batch_professor_list.add(single_object);
				}
			}catch (Exception e) {
				// TODO: handle exception
			}
			return batch_professor_list;
		}

public ArrayList<StudentListVo> getBatchStudentList(int batch_id, int degree_id)
		{
ArrayList<StudentListVo> batch_student_list= new ArrayList<StudentListVo>();
String query="";
	if(	batch_id	==	0)
		query="select sd.student_name," +
					" sd.student_id, " +
					" sd.city," +
					" sd.highest_degree," +
					" sd.gender," +
					" sd.years_of_experience," +
					" sd.registration_number" +
			   " from student_details sd; ";
	else 
	query="select sd.student_name," +
					" sd.student_id, " +
					" sd.city," +
					" sd.highest_degree," +
					" sd.gender," +
					" sd.years_of_experience," +
					" sd.registration_number" +
			  " from student_details sd " +
			  "where student_id in (select sb.student_id " +
			                         "from student_batch sb," +
			                               "batch_details bd " +
			                         "where bd.batch_id	=	sb.batch_id " +
			                         "  and sb.batch_id	=	"+batch_id+");";
			Connect c=new Connect();
			ResultSet rs= null;
			try {
				rs = c.getResult(query);		
				while(rs.next()){
					
					StudentListVo single_object = new StudentListVo();
					single_object.setStudent_id(rs.getInt("sd.student_id"));
					single_object.setStudent_name(rs.getString("sd.student_name"));
					single_object.setCity(rs.getString("sd.city"));
					single_object.setGender(rs.getString("sd.gender"));
					single_object.setHighest_degree(rs.getString("sd.highest_degree"));
					single_object.setWork_experience(rs.getInt("sd.years_of_experience"));
					single_object.setRegistration_number(rs.getString("sd.registration_number"));
					System.out.print("Studnet names"+single_object.getStudent_name());

					batch_student_list.add(single_object);
				}
			}catch (Exception e) {
				// TODO: handle exception
				System.out.print("error+"+e.getMessage());
				
			}
			return batch_student_list;
			
		}

public ArrayList<DegreeListVo> editBatchDegree(int degree_id,int batch_id){
	ArrayList<DegreeListVo> batch_degree_list= new ArrayList<DegreeListVo>();
	String query;
	query="select * from degree_management where batch_id="+batch_id+" and degree_id="+degree_id+"";
	System.out.println(query);
	Connect c= new Connect();
	try {
		ResultSet rs= c.getResult(query);
		while(rs.next()){
			DegreeListVo vo = new DegreeListVo();
			vo.setBatch_id(batch_id);
			vo.setDegree_id(degree_id);
			vo.setDegree_incharge_id(rs.getInt("degree_incharge_id"));
			vo.setDegree_management_id(rs.getInt("degree_management_id"));
			vo.setDescription(rs.getString("description"));
			vo.setIncharge_start_date(rs.getString("incharge_start_date"));
			vo.setIncharge_end_date(rs.getString("incharge_end_date"));
			vo.setStatus(rs.getString("status"));
			batch_degree_list.add(vo);
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	
	return batch_degree_list;
}
		
//Function to Generate list of degree that batch doesnot have
public ArrayList<DegreeListVo> getAddBatchDegree(int batch_id){
	ArrayList<DegreeListVo> add_batch_degree_list= new ArrayList<DegreeListVo>();
	String query;
	query="SELECT degree_id, degree_name FROM degree_details WHERE degree_id NOT IN ("
			+"SELECT DISTINCT degree_id FROM degree_department WHERE status='Active' and batch_id ="+batch_id+" )";
	Connect c= new Connect();
	try {
		ResultSet rs= c.getResult(query);
		while(rs.next()){
			DegreeListVo vo = new DegreeListVo();
			vo.setDegree_id(rs.getInt("degree_id"));
			vo.setDegree_name(rs.getString("degree_name"));
			add_batch_degree_list.add(vo);
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return add_batch_degree_list;
}
		
//Function to return the list of degree corresponding to particular batch
		public ArrayList<DegreeListVo> getBatchDegreeList(int batch_id) {
			
			ArrayList<DegreeListVo> batch_degree_list= new ArrayList<DegreeListVo>();
			String query="";	
			if(batch_id==0){
				query="select * from degree_details where status='Active'";
			}else{
				query="SELECT DISTINCT dd.degree_id, dd.degree_name "
					+"FROM degree_department dp, degree_details dd"
					+" WHERE batch_id ="+batch_id+" AND dd.degree_id = dp.degree_id AND dp.status='Active'";
			}
			System.out.println(query);
			Connect c= new Connect();
			ResultSet rs;
			try {
				rs = c.getResult(query);
				while(rs.next()){
					DegreeListVo vo = new DegreeListVo();
					vo.setDegree_id(rs.getInt("degree_id"));
					vo.setDegree_name(rs.getString("degree_name"));
					vo.setBatch_id(batch_id);
					batch_degree_list.add(vo);
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		
/*
if (batch_id==0)
			query="select  dm.degree_id," +
							      "bd.batch_name," +
							      "dm.batch_id, " +
							      "dd.degree_name " +
						   "from  degree_management dm," +
						   		  "degree_details dd," +
						   		  "batch_details bd " +
						   "where bd.batch_id	=	dm.batch_id" +
						    " and dd.degree_id	=	dm.degree_id";
else
			 query="select  dm.degree_id," +
							      "bd.batch_name," +
							      "dm.batch_id, " +
							      "dd.degree_name " +
						   "from  degree_management dm," +
						   		  "degree_details dd," +
						   		  "batch_details bd " +
						   "where bd.batch_id	=	dm.batch_id" +
						    " and dd.degree_id	=	dm.degree_id" +
						    " and bd.batch_id	=	"+batch_id+";";
						    */
			return batch_degree_list;
					}
		
public ArrayList<DegreeListVo> getDepartmentDegreeList(int department_id){
		ArrayList<DegreeListVo> department_degree_list = new ArrayList<DegreeListVo>();
		String query="";
		Connect c= new Connect();
		query="SELECT distinct dd.degree_id,degree_name, dd.description, dd.duration FROM degree_details dd, degree_department_mgmt dp "
			  +"WHERE dp.department_id ="+department_id+" AND dp.degree_id = dd.degree_id and dd.status='Active'";
		System.out.println(query);
		
		try {
			ResultSet rs= c.getResult(query);
			while (rs.next()){
				DegreeListVo vo = new DegreeListVo();
				vo.setDegree_id(rs.getInt("degree_id"));
				vo.setDegree_name(rs.getString("degree_name"));
				vo.setDescription(rs.getString("description"));
				vo.setDuration(rs.getInt("duration"));
				vo.setDepartment_id(department_id);
				department_degree_list.add(vo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
			
return department_degree_list;	
}		
		
public ArrayList<BatchDetailsVo> getDepartmentBatchList(int department_id){
	ArrayList<BatchDetailsVo> batchlist = new ArrayList<BatchDetailsVo>();
	String query="";
	Connect c= new Connect();
	query="SELECT distinct bd.batch_id, bd.batch_name, bd.description, bd.student_count FROM batch_details bd, degree_department dd "
		  +"WHERE department_id ="+department_id+" AND dd.batch_id = bd.batch_id AND dd.status = 'Active'";
	System.out.println(query);
	try {
		ResultSet rs= c.getResult(query);
		while(rs.next()){
		BatchDetailsVo vo = new BatchDetailsVo();
		vo.setBatch_name(rs.getString("batch_name"));
		vo.setBatch_id(rs.getInt("bd.batch_id"));
		vo.setDescription(rs.getString("description"));
		vo.setStudent_count(rs.getInt("student_count"));
		vo.setDepartment_id(department_id);
		batchlist.add(vo);
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return batchlist;
}	

public ArrayList<DegreeListVo> getAddDepartmentDegree(int department_id){
	ArrayList<DegreeListVo> degreelist = new ArrayList<DegreeListVo>();
	String query="";
	Connect c= new Connect();
	query="SELECT degree_id, degree_name, description, duration FROM degree_details WHERE degree_id NOT IN ( "
			+"SELECT degree_id FROM degree_department_mgmt WHERE department_id ="+department_id+")";
	
	try {
		ResultSet rs= c.getResult(query);
		while(rs.next()){
			DegreeListVo vo= new DegreeListVo();
			vo.setDegree_id(rs.getInt("degree_id"));
			vo.setDegree_name(rs.getString("degree_name"));
			vo.setDescription(rs.getString("description"));
			vo.setDuration(rs.getInt("duration"));
			vo.setDepartment_id(department_id);
			degreelist.add(vo);
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return degreelist;
}
public ArrayList<BatchDetailsVo> getAddDepartmentBatch(int department_id){
	ArrayList<BatchDetailsVo> batchlist = new ArrayList<BatchDetailsVo>();
	String query="";
	query="SELECT batch_id, batch_name FROM batch_details WHERE batch_id NOT IN (SELECT batch_id FROM degree_department WHERE department_id =1)";
	Connect c= new Connect();
	System.out.println(query);
	try {
		ResultSet rs=c.getResult(query);
		while(rs.next()){
			BatchDetailsVo vo = new BatchDetailsVo();
			vo.setBatch_id(rs.getInt("batch_id"));
			vo.setBatch_name(rs.getString("batch_name"));
			batchlist.add(vo);
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return batchlist;
}

public ArrayList<StudentListVo> getDegreeStudentList(int degree_id, int batch_id)
		{
			ArrayList<StudentListVo> student_list= new ArrayList<StudentListVo>();
		
			String query="";
			if (batch_id==0)
			query="select * " +
					     "  from " +
					     "       student_details " +
					     " where student_id in (select sb.student_id " +
					                            " from degree_department dd," +
					                            "      student_batch sb " +
					                            "where dd.batch_id	=	sb.batch_id " +
					                            "  and dd.degree_id="+degree_id+");";
			else
				query="select * " +
					     "  from " +
					     "       student_details " +
					     " where student_id in (select sb.student_id " +
					                            " from degree_department dd," +
					                            "      student_batch sb " +
					                            "where dd.batch_id	=	sb.batch_id " +
					                            "  and sb.batch_id="+batch_id+"" +
					                            "  and dd.degree_id="+degree_id+");";
			Connect c=new Connect();
			ResultSet rs= null;
			try {
				rs = c.getResult(query);	
				System.out.println(query+"  qury ");
				while(rs.next()){
					
					StudentListVo single_object = new StudentListVo();
					single_object.setStudent_id(rs.getInt("student_id"));
					single_object.setBatch_id(batch_id);
					single_object.setDegree_id(degree_id);
					single_object.setCity(rs.getString("city"));
					single_object.setGender(rs.getString("gender"));
					single_object.setWork_experience(rs.getInt("years_of_experience"));
					single_object.setStudent_name(rs.getString("student_name"));
					single_object.setHighest_degree(rs.getString("highest_degree"));
					System.out.println(single_object.getStudent_id()+"\n s id");
					student_list.add(single_object);
				}
			}catch (Exception e) {
				// TODO: handle exception
				System.out.print("error+"+e.getMessage());
				student_list	=	null;
				
			}
			return student_list;
		}
			
		
		
		public ArrayList<BatchDetailsVo> editbatchlist(int batch_id){
			String query;
			query="select * from batch_details where batch_id="+batch_id;
			System.out.println(query);
			Connect c= new Connect();
			try {
				ResultSet rs=c.getResult(query);
				while(rs.next()){
					BatchDetailsVo vo= new BatchDetailsVo();
					vo.setBatch_name(rs.getString("batch_name"));
					vo.setStart_date(rs.getString("start_date"));
					vo.setEnd_date(rs.getString("end_date"));
					vo.setStudent_count(rs.getInt("student_count"));
					vo.setStart_year(rs.getInt("start_year"));
					vo.setIncharge_id(rs.getInt("incharge_id"));
					vo.setDescription(rs.getString("description"));
					vo.setBatch_id(batch_id);
					batch_list.add(vo);
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return batch_list;
			
		}
		
		public void deletebatch(int batch_id,int degree_id,String type){
			String query="",query1="";
			Connect c= new Connect();
			query="Update degree_department set status='Inactive' where batch_id="+batch_id+" and degree_id="+degree_id+"";
			//query1="update degree_course set status='Inactive' where batch_id="+batch_id+" and degree_id="+degree_id+"";
			System.out.println(query);
			try {
				c.dml(query);
				//c.dml(query1);
				c.logger(type, "Update");
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
}
